package work.mediway.mdm.biz.service.biz.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.handler.BeanHandler;
import cn.hutool.db.handler.NumberHandler;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.json.JSONUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import work.mediway.mdm.core.helper.BaseDatabaseHelper;
import work.mediway.mdm.core.mapper.CommonMapper;
import work.mediway.mdm.core.page.QueryRequest;
import work.mediway.mdm.biz.service.biz.DictStatisticsJobService;
import work.mediway.mdm.core.constant.JobConstant;
import work.mediway.mdm.core.constant.MdmConstant;
import work.mediway.mdm.core.constant.SQLConstant;
import work.mediway.mdm.core.enums.StatusEnum;
import work.mediway.mdm.core.vo.TableVO;
import work.mediway.mdm.biz.dto.dict.CodeSystemDTO;
import work.mediway.mdm.biz.dto.dict.DictListDTO;
import work.mediway.mdm.biz.entity.biz.SsRoleLinkField;
import work.mediway.mdm.biz.entity.biz.SsRoleLinkTable;
import work.mediway.mdm.biz.entity.biz.UserBTEventLog;
import work.mediway.mdm.biz.entity.dict.CtCodeSystem;
import work.mediway.mdm.biz.helper.BusinessDatabaseHelper;
import work.mediway.mdm.biz.service.biz.SsRoleLinkFieldService;
import work.mediway.mdm.biz.service.biz.SsRoleLinkTableService;
import work.mediway.mdm.biz.service.biz.UserBTEventLogService;
import work.mediway.mdm.biz.service.dict.CtCodeSystemService;
import work.mediway.mdm.biz.vo.dict.DictFieldVO;
import work.mediway.mdm.biz.vo.dict.DictListVO;
import work.mediway.mdm.biz.vo.dict.StatisticsDictVO;

/**
 * @author zhongyj <1126834403@qq.com><br/>
 * @date 2022/9/1
 */
@SuppressWarnings("AlibabaAvoidComplexCondition")
@Slf4j
@Service
@Transactional(rollbackFor = Exception.class)
@RequiredArgsConstructor
public class DictStatisticsJobServiceImpl implements DictStatisticsJobService {

    private final CtCodeSystemService codeSystemService;
    private final UserBTEventLogService userBtEventLogService;
    private final BusinessDatabaseHelper businessDatabaseHelper;
    private final BaseDatabaseHelper baseDatabaseHelper;
    private final CtCodeSystemService ctCodeSystemService;
    private final SsRoleLinkTableService roleLinkTableService;
    private final SsRoleLinkFieldService roleLinkFieldService;
    private final CommonMapper commonMapper;

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void statisticsChange(String today) {
        today = Optional.of(today).orElse(DateUtil.today());
        List<CtCodeSystem> systemList = this.codeSystemService.list();
        // 先查询是否需要跑历史的统计
        List<UserBTEventLog> list = this.userBtEventLogService.list(Wrappers.lambdaQuery(UserBTEventLog.class)
                .eq(UserBTEventLog::getBtCode, JobConstant.CONST_DICT_CHANGE_KEY)
                .eq(UserBTEventLog::getLastUpdateDate, DateUtil.offsetDay(DateUtil.parseDate(today), -1)));
        // 推断每个表的日期字段
        Connection localConnection = SpringUtil.getBean(BaseDatabaseHelper.class).getLocalConnection();
        for (CtCodeSystem codeSystem : systemList) {
            List<DictFieldVO> column = this.businessDatabaseHelper.getColumn(localConnection, codeSystem.getCodeSystemCode());
            if (CollUtil.contains(column, "UpdateDate")) {
                codeSystem.setDateField("UpdateDate");
            } else {
                column.forEach(item -> {
                    if ((StrUtil.equalsAnyIgnoreCase(item.getSqlType(), "DATE") && StrUtil.containsAnyIgnoreCase(item.getFieldCode(), "update"))
                            || (StrUtil.equalsAnyIgnoreCase(item.getSqlType(), "datetime") && StrUtil.containsAnyIgnoreCase(item.getFieldCode(), "update"))) {
                        codeSystem.setDateField(item.getFieldCode());
                    }
                });
            }
        }
        // 如果为空，则需要统计历史数据
        if (CollUtil.isEmpty(list)) {
            statisticsHistory(systemList);
        } else {
            // 遍历每一个字典, 统计其信息
            int increase = 0;
            int total = 0;
            List<StatisticsDictVO> curr = statisticsCurr(today, systemList);
            for (StatisticsDictVO vo : curr) {
                increase += vo.getIncrease();
                total += vo.getTotalCount();
            }
            // 存入数据库
            UserBTEventLog eventLog = new UserBTEventLog(JobConstant.CONST_DICT_CHANGE_KEY, JSONUtil.toJsonStr(curr), today);
            eventLog.setBtIncrease(String.valueOf(increase));
            eventLog.setBtTotalNum(String.valueOf(total));
            this.userBtEventLogService.saveBtEventLog(eventLog);
        }
    }

    public void statisticsHistory(List<CtCodeSystem> systemList) {
        List<String> dates = CollUtil.newArrayList();
        // 查询任务表中对应最大日期
        Page<UserBTEventLog> page = new Page<>(1, 1);
        page.setOrders(OrderItem.descs("UpdateDate"));
        LambdaQueryWrapper<UserBTEventLog> queryWrapper = Wrappers.lambdaQuery(UserBTEventLog.class).eq(UserBTEventLog::getBtCode, JobConstant.CONST_DICT_CHANGE_KEY);
        Page<UserBTEventLog> btEventLogPage = this.userBtEventLogService.page(page, queryWrapper);
        if (CollUtil.isEmpty(btEventLogPage.getRecords())) {
            // 找字典表中的最开始日期
            Connection localConnection = SpringUtil.getBean(BaseDatabaseHelper.class).getLocalConnection();
            for (CtCodeSystem codeSystem : systemList) {
                StrBuilder minDateSql = new StrBuilder().append(SQLConstant.SQL_SELECT).append("MIN(").append(codeSystem.getDateField()).append(") AS minDate").append(SQLConstant.SQL_FROM).append(codeSystem.getCodeSystemCode());
                try {
                    String minDate = SqlExecutor.query(localConnection, minDateSql.toString(), new BeanHandler<>(String.class));
                    if (StrUtil.isNotBlank(minDate)) {
                        dates.add(minDate);
                    } else {
                        dates.add(DateUtil.today());
                    }
                } catch (Exception e) {
                    log.debug("跳过表: {}", codeSystem.getCodeSystemCode());
                }
            }
        } else {
            dates.add(btEventLogPage.getRecords().get(0).getLastUpdateDate());
        }

        List<DateTime> dateTimes = DateUtil.rangeToList(DateUtil.parseDate(CollUtil.min(dates)), DateUtil.parseDate(DateUtil.today()), DateField.DAY_OF_MONTH);
        for (DateTime currentDate : dateTimes) {
            UserBTEventLog current = this.userBtEventLogService.getOne(Wrappers.lambdaQuery(UserBTEventLog.class)
                    .eq(UserBTEventLog::getBtCode, JobConstant.CONST_DICT_CHANGE_KEY)
                    .eq(UserBTEventLog::getLastUpdateDate, currentDate));
            if (ObjectUtil.isEmpty(current)) {
                List<StatisticsDictVO> curr = statisticsCurr(DateUtil.formatDate(currentDate), systemList);
                UserBTEventLog eventLog = new UserBTEventLog(JobConstant.CONST_DICT_CHANGE_KEY, JSONUtil.toJsonStr(curr), DateUtil.formatDate(currentDate));
                int increase = 0;
                int total = 0;
                for (StatisticsDictVO vo : curr) {
                    increase += vo.getIncrease();
                    total += vo.getTotalCount();
                }
                eventLog.setBtIncrease(String.valueOf(increase));
                eventLog.setBtTotalNum(String.valueOf(total));
                // 存入数据库
                this.userBtEventLogService.saveOrUpdate(eventLog);
            }
        }
    }

    private List<StatisticsDictVO> statisticsCurr(String currDate, List<CtCodeSystem> systemList) {
        log.info(StrUtil.format("统计日期为：【{}】的字典信息数据开始", currDate));
        List<StatisticsDictVO> result = CollUtil.newArrayList();
        // 查询前一天历史数据总量new CodeSystemDTO
        UserBTEventLog userBtEventLog = this.userBtEventLogService.getOne(Wrappers.lambdaQuery(UserBTEventLog.class)
                .eq(UserBTEventLog::getBtCode, JobConstant.CONST_DICT_CHANGE_KEY)
                .eq(UserBTEventLog::getLastUpdateDate, DateUtil.formatDate(DateUtil.offsetDay(DateUtil.parseDate(currDate), -1))));
        List<StatisticsDictVO> details = null;
        if (ObjectUtil.isNotEmpty(userBtEventLog)) {
            details = JSONUtil.toList(JSONUtil.parseArray(userBtEventLog.getBtDetails()), StatisticsDictVO.class);
        }

        Connection localConnection = SpringUtil.getBean(BaseDatabaseHelper.class).getLocalConnection();
        for (CtCodeSystem codeSystem : systemList) {
            StatisticsDictVO vo = new StatisticsDictVO();
            vo.setDictCode(codeSystem.getCodeSystemCode());
            vo.setDictName(codeSystem.getCodeSystemDesc());
            // 先直接根据 UpdateDate 统计单独字典表的当天的数据变化的总量（包括新增和更新的数据）
            StrBuilder sql = new StrBuilder();
            sql.append("SELECT COUNT(*) AS totalUpdate FROM ").append(codeSystem.getCodeSystemCode());
            StrBuilder condition = new StrBuilder();
            if (StrUtil.isNotBlank(codeSystem.getDateField())) {
                condition.append(" WHERE ").append(codeSystem.getDateField()).append(" = '").append(currDate).append("'");
            } else {
                log.debug("表: {}未找到日期字段, 日期增量查询全部", codeSystem.getCodeSystemCode());
            }
            String exeCon = sql + condition.toString();
            try {
                Integer queryUpdate = SqlExecutor.query(localConnection, exeCon, new NumberHandler()).intValue();
                vo.setTotalUpdate(queryUpdate);
                log.debug("查询表: {}, sql: {}, 日期: {}, 更新数据总数: {}", codeSystem.getCodeSystemCode(), exeCon, currDate, vo.getTotalUpdate());
                // 查询当前数据总量
                Integer queryTotal = SqlExecutor.query(localConnection, sql.toString(), new NumberHandler()).intValue();
                vo.setTotalCount(queryTotal);
                log.debug("查询表: {}, sql: {}, 数据总数: {}", codeSystem.getCodeSystemCode(), sql, vo.getTotalCount());
                // 同时更新CodeSystemCode表信息
                CodeSystemDTO codeSystemDTO = new CodeSystemDTO();
                codeSystemDTO.setCodeSystemCode(codeSystem.getCodeSystemCode());
                codeSystemDTO.setDataCount(vo.getTotalCount());
                this.codeSystemService.updateDataCountByCode(codeSystemDTO);
                if (CollUtil.isNotEmpty(details)) {
                    assert details != null;
                    List<StatisticsDictVO> one = details.stream().filter(item -> StrUtil.equalsIgnoreCase(item.getDictCode(), codeSystem.getCodeSystemCode())).collect(Collectors.toList());
                    if (CollUtil.isNotEmpty(one)) {
                        StatisticsDictVO histDictVO = one.get(0);
                        // 新增数据 = 当前数据总量 - 历史数据总量
                        vo.setIncrease(vo.getTotalCount() - histDictVO.getTotalCount());
                    } else {
                        vo.setIncrease(0);
                    }
                } else {
                    vo.setIncrease(vo.getTotalUpdate());
                }
                // 更新数据 = 当天数据变化量 - 新增数据
                vo.setUpdate(vo.getTotalUpdate() - vo.getIncrease());
                log.info(StrUtil.format("统计表【{}】日期为：【{}】的数据增量信息为：【{}】", codeSystem.getCodeSystemCode(), currDate, vo));
                result.add(vo);
            } catch (Exception e) {
                if (StrUtil.containsIgnoreCase(e.getMessage(), "未找到表")) {
                    continue;
                }
                if (StrUtil.containsIgnoreCase(e.getMessage(), "相对应的表中没有找到字段")) {
                    continue;
                }
                e.printStackTrace();
            }
        }
        log.info(StrUtil.format("统计日期为：【{}】的字典信息数据完成", currDate));
        return result;
    }

    @Override
    public void statisticsDictTable(String schema) {
        List<TableVO> tables = this.baseDatabaseHelper.getTable(null);
        tables.forEach(item -> {
            boolean dictFlag = true;
            for (String dict : MdmConstant.MDM_EXCLUDE) {
                if (item.getTableCode().startsWith(dict)) {
                    dictFlag = false;
                    break;
                }
            }
            CtCodeSystem one = this.ctCodeSystemService.getOne(Wrappers.lambdaQuery(CtCodeSystem.class).eq(CtCodeSystem::getCodeSystemCode, item.getTableCode()));
            try {
                String count = this.commonMapper.getPublicItems("SELECT COUNT(*) AS COUNT FROM " + item.getTableCode()).get(0).get("COUNT").toString();
                if (ObjectUtil.isEmpty(one) && dictFlag) {
                    one = new CtCodeSystem();
                    one.setCodeSystemCode(item.getTableCode());
                    one.setCodeSystemDesc(item.getTableName());
                    one.setCodeSystemCat("2");
                    one.setStatus(StatusEnum.STATUS_ACTIVE.getStatusCode());
                    one.setDataCount(Integer.valueOf(count));
                    this.ctCodeSystemService.saveCodeSystem(one);
                } else if (dictFlag && ObjectUtil.isNotEmpty(one)) {
                    one.setCodeSystemCode(item.getTableCode());
                    one.setCodeSystemDesc(item.getTableName());
                    one.setDataCount(Integer.valueOf(count));
                    this.ctCodeSystemService.updateById(one);
                }
                if (dictFlag) {
                    // 管理员用户每张表自动分配所有权限
                    log.info("为管理员分配表【{}】权限", item.getTableCode());
                    this.roleLinkTableService.autoAddPermForAdmin(item);

                    /*LambdaQueryWrapper<SsModelInfo> updateWrapper = Wrappers.lambdaQuery(SsModelInfo.class)
                            .eq(SsModelInfo::getModelCode, item.getTableCode());
                    SsModelInfo modelInfo = SpringUtils.getBean(SsModelInfoService.class).getOne(updateWrapper);
                    Map<String, Object> paramMap = MapUtil.newHashMap();
                    paramMap.put("tableCode", item.getTableCode());
                    paramMap.put("pageNumber", "1");
                    paramMap.put("pageSize", "10");
                    modelInfo.setSelectRequest(JSONUtil.toJsonPrettyStr(paramMap));
                    SpringUtils.getBean(SsModelInfoService.class).updateById(modelInfo);*/
                }
            } catch (Exception e) {
                if (StrUtil.contains(e.getMessage(), "CTCS_Desc") && StrUtil.contains(e.getMessage(), "未通过唯一性检查")) {
                    one.setCodeSystemDesc(one.getCodeSystemCode());
                    try {
                        this.ctCodeSystemService.save(one);
                        this.roleLinkTableService.autoAddPermForAdmin(item);
                    } catch (Exception e1) {
                        log.error(e1.getMessage());
                    }
                } else {
                    log.error(e.getMessage());
                }
            }
        });
        log.info("开始删除已经被本地删除的表信息");
        List<String> codeSystems = this.ctCodeSystemService.pageCodeSystemAll(new DictListDTO(), new QueryRequest()).getRecords().stream().map(DictListVO::getCodeSystemCode).collect(Collectors.toList());
        List<String> tableCodes = tables.stream().map(TableVO::getTableCode).collect(Collectors.toList());
        for (String codeSystem : codeSystems) {
            if ((!CollUtil.contains(tableCodes, codeSystem))) {
                removeTable(codeSystem);
                continue;
            }
            List<String> exclude = CollUtil.newArrayList(MdmConstant.MDM_EXCLUDE);
            for (String exc : exclude) {
                if (StrUtil.startWithIgnoreCase(codeSystem, exc)) {
                    removeTable(codeSystem);
                    break;
                }
            }
        }
        List<SsRoleLinkTable> roleLinkTables = this.roleLinkTableService.list();
        for (SsRoleLinkTable roleLinkTable : roleLinkTables) {
            if ((!tableCodes.contains(roleLinkTable.getSsTable()))) {
                removeTable(roleLinkTable.getSsTable());
            }
        }
    }

    private void removeTable(String codeSystem) {
        log.info("删除表: {}", codeSystem);
        this.ctCodeSystemService.remove(Wrappers.lambdaUpdate(CtCodeSystem.class).eq(CtCodeSystem::getCodeSystemCode, codeSystem));
        this.roleLinkTableService.remove(Wrappers.lambdaUpdate(SsRoleLinkTable.class).eq(SsRoleLinkTable::getSsTable, codeSystem));
        this.roleLinkFieldService.remove(Wrappers.lambdaUpdate(SsRoleLinkField.class).eq(SsRoleLinkField::getSsTable, codeSystem));
    }
}
